package com.ruoyi.generator.service;

import java.io.StringWriter;
import java.util.*;

import com.ruoyi.common.constant.Constants;
import com.ruoyi.common.utils.DateUtils;
import com.ruoyi.generator.domain.GenTable;
import com.ruoyi.generator.domain.GenTableColumn;
import com.ruoyi.generator.domain.GenTableColumnSql;
import com.ruoyi.generator.domain.dto.YunxinSql;
import com.ruoyi.generator.mapper.GenTableColumnMapper;
import com.ruoyi.generator.mapper.GenTableColumnSqlMapper;
import com.ruoyi.generator.mapper.GenTableMapper;
import com.ruoyi.generator.util.DataPermissionSqlUtil;
import com.ruoyi.generator.util.GenSqlUtils;
import com.ruoyi.generator.util.VelocityInitializer;
import com.ruoyi.generator.util.VelocityUtils;
import org.apache.velocity.Template;
import org.apache.velocity.VelocityContext;
import org.apache.velocity.app.Velocity;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.ruoyi.generator.mapper.GenTableSqlMapper;
import com.ruoyi.generator.domain.GenTableSql;
import com.ruoyi.common.annotation.CacheZZ;
import com.ruoyi.common.annotation.CacheZZRemove;
import com.ruoyi.common.annotation.CacheZZTable;

/**
 * 代码生成业务Service业务层处理
 *
 * @author 众智汇云研发部
 * @date 2021-11-23
 */
@Service
public class GenTableSqlService{

    public final static String gen_table_sql = "gen_table_sql";

    public final static String gen_table_sql_table = "gen_table_sql_table";

    @Autowired
    private GenTableSqlMapper genTableSqlMapper;
    @Autowired
    private IGenTableService genTableService;
    @Autowired
    private GenTableMapper genTableMapper;
    @Autowired
    private GenTableColumnMapper genTableColumnMapper;
    @Autowired
    private GenTableColumnSqlMapper genTableColumnSqlMapper;


    /**
     * 运行sql
     *
     * @param genTable sql
     * @return 业务集合
     */
    public int yunxinSql(YunxinSql yunxinSql){
        try {
            // 运行sql
            List<Map<String, Object>> maps = genTableSqlMapper.yunxinSql(yunxinSql.getSql());
        } catch (Exception e){
            e.printStackTrace();
            return -1;
        }
        return 0;
    }
    // 保存sql用到的列
    public List<GenTableColumnSql> insertColumn(List<String> tablenames,List<GenTableSql> genTableSqls,String jiexisql){

        List<GenTableColumnSql> resList = new ArrayList<>();
        // 字段类型
        String[] tablenamesArr = new String[tablenames.size()];
        tablenames.toArray(tablenamesArr);
        List<GenTable> comlist = genTableService.selectDbTableListByNames(tablenamesArr);
        // 根据别名存字段
        Map<String,List<GenTableColumn>> columnMapas = new HashMap<>();
        // 根据表名存字段
        Map<String,List<GenTableColumn>> columnMap = new HashMap<>();
        // 表别名对应的表名
        Map<String,String> tableAsName = new HashMap<>();
        for (GenTableSql genTableSql : genTableSqls) {
            for (GenTable table : comlist)
            {
                if(genTableSql.getTableName().equalsIgnoreCase(table.getTableName())){
                    List<GenTableColumn> columnList = new ArrayList<>();
                    // 查询列信息
                    List<GenTableColumn> genTableColumns = genTableColumnMapper.selectDbTableColumnsByName(table.getTableName());
                    for (GenTableColumn column : genTableColumns)
                    {
                        GenSqlUtils.initColumnField(column, table);
                        columnList.add(column);
                    }
                    if (!"".equals(genTableSql.getSubTableName())){
                        columnMapas.put(genTableSql.getSubTableName(),columnList);
                        tableAsName.put(genTableSql.getSubTableName(),table.getTableName());
                    }
                    columnMap.put(table.getTableName(),columnList);
                }
            }
        }
        Integer stringColumunum = 1;
        // 获取sql使用表的字段
        List<String> selectColumns = DataPermissionSqlUtil.getSelectColumns(jiexisql);
        for (String selectColumn : selectColumns) {
            // 判断字段是否使用了别名
            String asName = "";
            if(selectColumn.indexOf(" ") != -1 || selectColumn.indexOf("AS") != -1){
                asName = selectColumn.substring(selectColumn.lastIndexOf(" ")).trim();
                selectColumn = selectColumn.substring(0,selectColumn.lastIndexOf(asName)).trim();
                selectColumn = selectColumn.substring(0,selectColumn.lastIndexOf("AS")).trim();
            }
            // 判断字段在不在表里
            boolean columuIsInTable = false;
            String stringColumuName = "columuName";
            // 判断查询的是不是*
            if(selectColumn.indexOf("*") != -1 && selectColumn.indexOf("(") == -1){
                // 判断是否使用了表
                if(selectColumn.indexOf(".") != -1){
                    String columuTableName = selectColumn.substring(0,selectColumn.indexOf("."));
                    for (GenTableSql genTableSql : genTableSqls) {
                        if(columuTableName.equalsIgnoreCase(genTableSql.getSubTableName())){
                            List<GenTableColumn> genTableColumns = genTableColumnMapper.selectDbTableColumnsByName(genTableSql.getTableName());
                            String[] tablenamesSearch = new String[1];
                            tablenamesSearch[0] = genTableSql.getTableName();
                            GenTable getTable = genTableService.selectDbTableListByNames(tablenamesSearch).get(0);
                            for (GenTableColumn column : genTableColumns) {
                                GenSqlUtils.initColumnField(column, getTable);
                                resList.add(addColumu(column,asName));
                                columuIsInTable = true;
                            }
                        }
                    }
                } else {
                    for (String tablename : tablenamesArr) {
                        List<GenTableColumn> genTableColumns = genTableColumnMapper.selectDbTableColumnsByName(tablename);
                        String[] tablenamesSearch = new String[1];
                        tablenamesSearch[0] = tablename;
                        GenTable getTable = genTableService.selectDbTableListByNames(tablenamesSearch).get(0);
                        for (GenTableColumn column : genTableColumns) {
                            GenSqlUtils.initColumnField(column, getTable);
                            resList.add(addColumu(column,asName));
                            columuIsInTable = true;
                        }
                    }
                }
            }
            // 判断字段是否使用了表前缀
            else if(selectColumn.indexOf(".") != -1 && selectColumn.indexOf("(") == -1){
                String columnName = selectColumn.substring(selectColumn.indexOf(".")+1);
                String tableAs = selectColumn.substring(0,selectColumn.indexOf("."));
                // 判断表前缀在不在表别名中如果不在就用表名查
                Integer asCount = 0;
                for (String as : columnMapas.keySet()) {
                    if(as.equalsIgnoreCase(tableAs)){
                        asCount ++;
                        // 根据表别名获取字段
                        List<GenTableColumn> genTableColumns = columnMapas.get(as);
                        // 根据表别名获取表名
                        String tableName = tableAsName.get(as);
                        // 字段类型
                        String[] tablenamesSearch = new String[1];
                        tablenamesSearch[0] = tableName;
                        GenTable getTable = genTableService.selectDbTableListByNames(tablenamesSearch).get(0);
                        for (GenTableColumn column : genTableColumns) {
                            GenSqlUtils.initColumnField(column, getTable);
                            if(column.getColumnName().equalsIgnoreCase(columnName)){
                                resList.add(addColumu(column,asName));
                                columuIsInTable = true;
                            }
                        }
                    }
                }
                // 如果用的不是表别名 就是用的表名
                if(asCount == 0){
                    for (String tableName : columnMap.keySet()) {
                        // 对比表名
                        if(tableName.equals(tableAs)){
                            // 根据表名获取字段
                            List<GenTableColumn> genTableColumns = columnMap.get(tableName);
                            // 字段类型
                            String[] tablenamesSearch = new String[1];
                            tablenamesSearch[0] = tableName;
                            GenTable getTable = genTableService.selectDbTableListByNames(tablenamesSearch).get(0);
                            for (GenTableColumn column : genTableColumns) {
                                GenSqlUtils.initColumnField(column, getTable);
                                if(column.getColumnName().equalsIgnoreCase(columnName)){
                                    resList.add(addColumu(column,asName));
                                    columuIsInTable = true;
                                }
                            }
                        }
                    }
                }
            }
            // 如果字段没有加前缀就全部字段匹配一遍
            else if(selectColumn.indexOf("(") == -1){
                for (String tableName : columnMap.keySet()) {
                    List<GenTableColumn> genTableColumns = columnMap.get(tableName);
                    for (GenTableColumn genTableColumn : genTableColumns) {
                        if(selectColumn.equalsIgnoreCase(genTableColumn.getColumnName())){
                            resList.add(addColumu(genTableColumn,asName));
                            columuIsInTable = true;
                        }
                    }
                }
            }
            // 判断有括号就是函数
            else if(selectColumn.indexOf("(") != -1){
                String hanshuType = DataPermissionSqlUtil.getHanshuType(selectColumn);
                GenTableColumnSql genTableColumnSql = new GenTableColumnSql();
                // 如果这个函数没有起别名就去掉符号当别名
                if(asName == null){
                    genTableColumnSql.setAsname(DataPermissionSqlUtil.deleteBiaoDian(selectColumn));
                } else {
                    genTableColumnSql.setAsname(asName);
                }
                if(genTableColumnSql.getComment() == null || "".equals(genTableColumnSql.getComment())){
                    genTableColumnSql.setComment(asName);
                }
                genTableColumnSql.setName(selectColumn);
                genTableColumnSql.setField(asName);
                genTableColumnSql.setJavaType(hanshuType);
                resList.add(genTableColumnSql);
                columuIsInTable = true;
            }
            // 如果表里字段找不到也不是函数就按字符串处理
            if(!columuIsInTable) {
                GenTableColumnSql genTableColumnSql = new GenTableColumnSql();
                if(selectColumn.indexOf("'") == -1){
                    genTableColumnSql.setJavaType("Integer");
                    genTableColumnSql.setAsname(selectColumn);
                    genTableColumnSql.setField(stringColumuName+stringColumunum);
                } else {
                    genTableColumnSql.setJavaType("String");
                    genTableColumnSql.setAsname(selectColumn.replace("'",""));
                    genTableColumnSql.setField(stringColumuName+stringColumunum);
                }
                genTableColumnSql.setComment(selectColumn);
                stringColumunum ++;
                resList.add(genTableColumnSql);
            }
        }
        return resList;
    }

    private GenTableColumnSql addColumu(GenTableColumn column,String asName) {
        GenTableColumnSql genTableColumnSql = new GenTableColumnSql();
        genTableColumnSql.setName(column.getColumnName());
        genTableColumnSql.setComment(column.getColumnComment());
        genTableColumnSql.setColumnType(column.getColumnType());
        genTableColumnSql.setJavaType(column.getJavaType());
        genTableColumnSql.setPk(column.getIsPk());
        genTableColumnSql.setIncrement(column.getIsIncrement());
        genTableColumnSql.setRequired(column.getIsRequired());
        genTableColumnSql.setInsert(column.getIsInsert());
        genTableColumnSql.setEdit(column.getIsEdit());
        genTableColumnSql.setList(column.getIsList());
        genTableColumnSql.setQuery(column.getIsQuery());
        genTableColumnSql.setQueryType(column.getQueryType());
        genTableColumnSql.setHtmlType(column.getHtmlType());
        genTableColumnSql.setDictType(column.getDictType());
        genTableColumnSql.setSort(Long.valueOf(column.getSort()));
        if(!"".equals(asName)){
            genTableColumnSql.setField(DataPermissionSqlUtil.lineToHump(asName));
        } else {
            genTableColumnSql.setField(column.getJavaField());
        }
        genTableColumnSql.setAsname(asName);
        if(genTableColumnSql.getAsname() == null || "".equals(genTableColumnSql.getAsname()) ){
            genTableColumnSql.setAsname(genTableColumnSql.getName());
        }
        if(genTableColumnSql.getComment() == null || "".equals(genTableColumnSql.getComment())){
            genTableColumnSql.setComment(genTableColumnSql.getAsname());
        }
        return genTableColumnSql;
    }

    // 保存sql用到的表
    public List<GenTableSql> insertTable(YunxinSql yunxinSql,List<String> tablenames,String operName) throws Exception {
        List<GenTableSql> genTableSqlList = new ArrayList<>();
        String jiexisql = yunxinSql.getSql().toLowerCase();

        String sqlFrom = DataPermissionSqlUtil.getSqlFrom(jiexisql);
        // 判断是否有子查询
        if(sqlFrom.indexOf("(") != -1){
            String[] tableFrom = sqlFrom.split("\\r?\\n");
            boolean ziChaXun = false;
            String ziChaXunSql = "";
            List<String> ziChaXunSqls = new ArrayList<>();
            // sql取出子查询
            for (String tableHang : tableFrom) {
                if(tableHang.indexOf("JOIN") != -1 && tableHang.indexOf("(") != -1 ){
                    ziChaXun = true;
                }
                if(tableHang.indexOf(")") == 1 ){
                    ziChaXun = false;
                    ziChaXunSql += tableHang;
                    ziChaXunSqls.add(ziChaXunSql);
                    ziChaXunSql = "";
                }
                if (ziChaXun){
                    sqlFrom = sqlFrom.replace(tableHang,"").replace("\\r?\\n","");
                    ziChaXunSql += tableHang;
                }
            }
            for (String chaXunSql : ziChaXunSqls) {
                String asName = chaXunSql.substring(chaXunSql.lastIndexOf(")")+1).trim();
                chaXunSql = chaXunSql.substring(chaXunSql.indexOf("(")+1,chaXunSql.lastIndexOf(")"));
                List<String> tableNames = DataPermissionSqlUtil.getTableNames(chaXunSql);
                for (String tableName : tableNames) {
                    // 获取表的别名
                    GenTableSql genTableSql = new GenTableSql();
                    genTableSql.setPackageName(yunxinSql.getPackageName());
                    genTableSql.setBusinessName(yunxinSql.getBusinessName());
                    genTableSql.setFunctionName(yunxinSql.getFunctionName());
                    genTableSql.setCreateBy(operName);
                    genTableSql.setRemark(yunxinSql.getSql());
                    genTableSql.setTableName(tableName);
                    // 表别名
                    genTableSql.setSubTableName(asName);
                    genTableSqlList.add(genTableSql);
                }
            }

        }
        if(sqlFrom.indexOf(",") == -1){
            String[] tablenameas = sqlFrom.split("\\r?\\n");
            for (String tablenamea : tablenameas) {
                for (String tablename : tablenames) {
                    if(tablenamea.toLowerCase().indexOf(tablename.toLowerCase()) != -1){
                        // 查询表字段类型
                        // 获取表的别名
                        String substring = tablenamea.substring(tablenamea.toLowerCase().indexOf(tablename.toLowerCase()));
                        if(substring.indexOf("ON") != -1){
                            substring = substring.substring(0,substring.indexOf("ON"));
                        }
                        substring = substring.substring(tablename.length()).trim();
                        GenTableSql genTableSql = new GenTableSql();
                        genTableSql.setPackageName(yunxinSql.getPackageName());
                        genTableSql.setBusinessName(yunxinSql.getBusinessName());
                        genTableSql.setFunctionName(yunxinSql.getFunctionName());
                        genTableSql.setCreateBy(operName);
                        genTableSql.setRemark(yunxinSql.getSql());
                        genTableSql.setTableName(tablename);
                        // 表别名
                        genTableSql.setSubTableName(substring);
                        genTableSqlList.add(genTableSql);
                    }
                }
            }
        } else {
            String[] tablenameas = sqlFrom.split(",");
            for (String tablenamea : tablenameas) {
                for (String tablename : tablenames) {
                    if(tablenamea.toLowerCase().indexOf(tablename.toLowerCase()) != -1){
                        // 获取表的别名
                        String substring = tablenamea.substring(tablenamea.toLowerCase().indexOf(tablename.toLowerCase()));
                        substring = substring.substring(tablename.length()).trim();
                        GenTableSql genTableSql = new GenTableSql();
                        genTableSql.setPackageName(yunxinSql.getPackageName());
                        genTableSql.setBusinessName(yunxinSql.getBusinessName());
                        genTableSql.setFunctionName(yunxinSql.getFunctionName());
                        genTableSql.setCreateBy(operName);
                        genTableSql.setRemark(yunxinSql.getSql());
                        genTableSql.setTableName(tablename);
                        // 表别名
                        genTableSql.setSubTableName(substring);
                        genTableSqlList.add(genTableSql);
                    }
                }
            }
        }


        return genTableSqlList;
    }



    /**
     * 查询代码生成业务
     *
     * @param id 代码生成业务ID
     * @return 代码生成业务
     */
    @CacheZZ(hashKey = gen_table_sql,timeOut = 60*60*24)
    public GenTableSql selectGenTableSqlById(Long id) {
        return genTableSqlMapper.selectById(id);
    }

    /**
     * 查询代码生成业务列表
     *
     * @param genTableSql 代码生成业务
     * @return 代码生成业务
     */
    @CacheZZTable(hashKey = gen_table_sql_table, timeOut = 60*60*8)
    public List<GenTableSql> selectGenTableSqlList(GenTableSql genTableSql) {
        return genTableSqlMapper.selectList(genTableSql);
    }

    /**
     * 新增代码生成业务
     *
     * @param genTableSql 代码生成业务
     * @return 结果
     */
    @CacheZZRemove(CacheZZTable = gen_table_sql_table)
    public int insertGenTableSql(GenTableSql genTableSql) {
        genTableSql.setCreateTime(DateUtils.getNowDate());
        genTableSql.setCreateTime(DateUtils.getNowDate());
        return genTableSqlMapper.insert(genTableSql);
    }

    /**
     * 修改代码生成业务
     *
     * @param genTableSql 代码生成业务
     * @return 结果
     */
    @CacheZZRemove(CacheZZTable = gen_table_sql_table,CacheZZ = gen_table_sql,updateKey = "#genTableSql.id")
    public int updateGenTableSql(GenTableSql genTableSql) {
        return genTableSqlMapper.update(genTableSql);
    }

    /**
     * 批量删除代码生成业务
     *
     * @param ids 需要删除的代码生成业务ID
     * @return 结果
     */
    @CacheZZRemove(CacheZZTable = gen_table_sql_table,CacheZZ = gen_table_sql)
    public int deleteGenTableSqlByIds(Long[] ids) {
        return genTableSqlMapper.deleteByIds(ids);
    }

}
